import akshare as ak
import pymysql
import time
import re
import jieba
from collections import Counter

# 连接 MySQL 数据库
conn = pymysql.connect(
    user='root',
    passwd='123456',
    db='bs',
    host='127.0.0.1',
    port=3306,
    charset='utf8',
    autocommit=True  # 自动提交确认
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

# 查询股票代码和流通股本数据
sql = 'SELECT CODE,Cshares FROM DATA;'
cursor.execute(sql)
data = list(cursor.fetchall())

# 关闭游标和数据库连接
cursor.close()
conn.close()

# 循环遍历每个股票，获取股票新闻，并进行情感分析
n = 1
for row in data:
    code, Cshares = row[0], row[1]
    stock_symbol = code.lower()
    try:
        # 检索过去一年的股票价格
        stock_df = ak.stock_zh_a_daily(symbol=stock_symbol, start_date='2022-04-29', end_date='2023-04-29')
        # 检索最近一周的新闻
        news_df = ak.stock_news_em(symbol=stock_symbol).head(100)
        # 将新闻标题转换为字符串列表
        news_titles = list(news_df['新闻标题'])
        print(news_titles)

        # 将新闻标题列表连接成一个字符串
        news_str = ' '.join(news_titles)

        # 使用正则表达式去除标点符号和数字
        news_str = re.sub(r'[^\w\s]|\d', '', news_str)

        # 将字符串转换为小写字母
        news_str = news_str.lower()

        # 使用 jieba 进行分词，并去除停用词
        stopwords = [line.strip() for line in open('通过接口获取数据\\stopwords.txt', 'r', encoding='utf-8').readlines()] # 停用词表
        words = jieba.lcut(news_str)
        words = [word for word in words if word not in stopwords]

        # 对分词结果进行词频统计
        word_counts = Counter(words)
        hot_words = word_counts.most_common(20) # 选取出现频率最高的前15个词汇

        # 将新闻标题保存到本地 txt 文件
        with open('通过接口获取数据\\news_titles.txt', 'a', encoding='utf-8') as f:
            for title in news_titles:
                f.write(title + '\n')

        print('Success! 这是第{}条'.format(n))
    except Exception as e:
        error_message = str(e)
        print('Error: {}'.format(error_message))
        # 将异常信息记录到日志文件
        with open('通过接口获取数据\\error.log', 'a', encoding='utf-8') as f:
            f.write('Error: {}\n'.format(error_message))

    n += 1
    time.sleep(0.5)

# 将热门词汇写入 MySQL 数据库
conn = pymysql.connect(host="localhost", user="root", password="123456", db="bs", charset="utf8")
cursor = conn.cursor()
for word, count in hot_words:
    sql = "INSERT INTO word (word, count) VALUES (%s, %s)"
    cursor.execute(sql, (word, count))
conn.commit()
cursor.close()
conn.close()

print('处理完毕!')